package com.anolesoft.amljmes.billing.service.mapper;

import cn.hutool.core.util.StrUtil;
import com.anolesoft.amljmes.billing.dto.AmBillingCondition;
import com.anolesoft.amljmes.billing.dto.AmBillingInfoDO;
import com.anolesoft.amljmes.billing.dto.AmBillingInfoDTO;
import com.anolesoft.amljmes.billing.dto.EmpInfoDTO;
import com.baomidou.mybatisplus.annotation.InterceptorIgnore;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.LocalDateTypeHandler;

import java.util.List;

@Mapper
public interface AmBillingInfoServiceMapper {
	@SelectProvider(type=createSQL.class, method = "queryAmBillingInfoDTOsSQL")
	@InterceptorIgnore(tenantLine = "true")
	List<AmBillingInfoDTO> queryAmBillingInfoDTOs(@Param("amBilingCondition")AmBillingCondition amBilingCondition, Page<AmBillingInfoDTO> page);

	@SelectProvider(type=createSQL1.class, method = "queryAmBillingInfoDTOsSQL1")
	@InterceptorIgnore(tenantLine = "true")
	List<AmBillingInfoDTO> queryAmBillingInfoDTOs2(@Param("amBilingCondition")AmBillingCondition amBilingCondition, Page<AmBillingInfoDTO> page);

	@SelectProvider(type=createSQL2.class, method = "queryAmBillingInfoDTOsSQL2")
	@InterceptorIgnore(tenantLine = "true")
	List<AmBillingInfoDTO> queryAllByConditon(@Param("amBilingCondition")AmBillingCondition amBilingCondition);


	
	public static class createSQL {
		public static String queryAmBillingInfoDTOsSQL(@Param("amBilingCondition")AmBillingCondition amBilingCondition) {
			return new SQL() {
				{
					StringBuilder sql = new StringBuilder();
					sql.append("    t.objuid,");
					sql.append("         t.pacr_no,");
					sql.append("         t.aircraft_type,");  
					sql.append("         t.part_drawing_no,");  
					sql.append("         t.part_name,");  
					sql.append("         t.dispatch_no,");
					sql.append("         t.lupihao,");
					sql.append("         t.quantity,");  
					sql.append("         t.handover_category,");  
					sql.append("         t.workshop_name,");  
					sql.append("         t.billing_user,");
					sql.append("         t.billing_deft,");  
					sql.append("         t.billing_time,");  
					sql.append("         t.receive_user,");  
					sql.append("         t.receive_dept,");  
					sql.append("         t.receive_time,");  
					sql.append("         t.receive_notes,");  
					sql.append("         t.receive_state,");  
					sql.append("         t.factory_id,");  
					sql.append("         t.billing_notes,");
					sql.append("         t.current_process_no,");
					sql.append("         t.current_process_name,");
					sql.append("         t.refuse_reason,t.c_user,t.c_time,t.m_user,t.m_time,");
					sql.append("		 t1.name billing_user_name,");//开单人姓名
					sql.append("		 t2.name receive_user_name,");//接单人
					sql.append("		 t3.dept_name billing_deft_name,");//开单部门
					sql.append("		 t4.dept_name receive_dept_name, ");//接单部门
					sql.append("		 t5.category_name handoverCategoryName, ");//移交类别
					sql.append("		 t6.workshop_name workshopNameName, ");//外表车间
					sql.append("		 t7.dept_name place_dept_name ");//所在部门
					sql.append("    from am_billing_info t ");
					sql.append("    left join jcwh_stk_category t5 on t.handover_category=t5.id  ");
					sql.append("    left join jcwh_stk_workshop t6 on t.workshop_name=t6.id  ");
//					sql.append("    left join mes_order_trace a on t.pacr_no = a.fabrecordid");
//					sql.append("   left join ( select ordertraceuid,onesoknum,goodnum,row_number() over (order by lcheckertime ) m from mes_process_trace ) b on a.objuid = b.ordertraceuid ");
					sql.append("	left join stf_employee t1 on t.billing_user=t1.objuid ");
					sql.append("	left join stf_employee t2 on t.receive_user=t2.objuid ");
					sql.append("	left join stf_dept t3 on t.billing_deft=t3.objuid ");
					sql.append("	left join stf_dept t4 on t.receive_dept=t4.objuid ");
					sql.append("	left join stf_dept t7 on t.receive_dept=t7.objuid ");
					sql.append("	where 1=1 ");
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getFactoryId())) {//厂代号
						sql.append(" and t.factory_id = #{amBilingCondition.factoryId, jdbcType=VARCHAR}");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getPacrNo())) {//条码号
						sql.append(" and upper(t.pacr_no) = upper(#{amBilingCondition.pacrNo, jdbcType=VARCHAR})");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getAircraftType())) {//机型
						sql.append(" and t.aircraft_type = #{amBilingCondition.aircraftType, jdbcType=VARCHAR}");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getPartDrawingNo())) {//零件图号
//						sql.append(" and t.part_drawing_no = #{amBilingCondition.partDrawingNo, jdbcType=VARCHAR}");
						sql.append(" and t.part_drawing_no like concat(concat('%',#{amBilingCondition.partDrawingNo, jdbcType=VARCHAR}),'%')");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getHandoverCategory())) {//移交类别
						sql.append(" and t.handover_category = #{amBilingCondition.handoverCategory, jdbcType=VARCHAR}");
					}					
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getWorkshopName())) {//外表车间
						sql.append(" and t.workshop_name = #{amBilingCondition.workshopName, jdbcType=VARCHAR}");
					}					
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getBillingUser())) {//开单人
//						sql.append(" and t.billing_user = #{amBilingCondition.billingUser, jdbcType=VARCHAR}");
						sql.append(" and t1.name like concat(concat('%',#{amBilingCondition.billingUser, jdbcType=VARCHAR}),'%')");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getBillingDeft())) {//开单部门
						sql.append(" and t.billing_deft = #{amBilingCondition.billingDeft, jdbcType=VARCHAR}");
					}
					if(amBilingCondition.getBillingTime()!= null) {//开单时间段
//						sql.append(" and to_date(to_char(t.billing_time,'yyyy-MM-dd'),'yyyy-MM-dd') >= to_date(to_char(#{amBilingCondition.billingTime, jdbcType=TIMESTAMP},'yyyy-MM-dd'),'yyyy-MM-dd') ");
						sql.append(" and trunc(t.billing_time) >= trunc(#{amBilingCondition.billingTime, jdbcType=TIMESTAMP})");
					}
					if(amBilingCondition.getBillingTime2()!= null) {
//						sql.append(" and to_date(to_char(t.billing_time,'yyyy-MM-dd'),'yyyy-MM-dd') <= to_date(to_char(#{amBilingCondition.billingTime2, jdbcType=TIMESTAMP},'yyyy-MM-dd'),'yyyy-MM-dd') ");
						sql.append(" and trunc(t.billing_time) < trunc(#{amBilingCondition.billingTime2, jdbcType=TIMESTAMP})+1");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getReceiveUser())) {//接收人
//						sql.append(" and t.receive_user = #{amBilingCondition.receiveUser, jdbcType=VARCHAR}");
						sql.append(" and t2.name like concat(concat('%',#{amBilingCondition.receiveUser, jdbcType=VARCHAR}),'%')");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getReceiveDept())) {//接收部门
						sql.append(" and t.receive_dept = #{amBilingCondition.receiveDept, jdbcType=VARCHAR}");
					}
					if(amBilingCondition.getReceiveTime()!= null) {//接收时间段
						sql.append(" and t.receive_time >= #{amBilingCondition.receiveTime, jdbcType=TIMESTAMP}");
					}
					if(amBilingCondition.getReceiveTime2()!= null) {
						sql.append(" and t.receive_time <= #{amBilingCondition.receiveTime2, jdbcType=TIMESTAMP}");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getReceiveState())) {//状态
						sql.append(" and t.receive_state = #{amBilingCondition.receiveState, jdbcType=VARCHAR}");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getRefuseReason())) {//拒绝原因
						sql.append(" and t.refuse_reason = #{amBilingCondition.refuseReason, jdbcType=VARCHAR}");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getPlaceDept())) {//所在部门
						sql.append(" and t.receive_dept = #{amBilingCondition.placeDept, jdbcType=VARCHAR} and t.receive_state = 5");
					}
//					System.out.println((amBilingCondition.getQueryType()==1)+"=======================================");
//					if(amBilingCondition.getQueryType()==1) {//查询类别 0 查询所有 不用加调价  1 查询最新的移交信息
////						sql.append(" and c_time >= #{amBilingCondition.cTime} and c_time <= #{amBilingCondition.cTime2} ");
//						sql.append(" order by c_time desc ");
//					}
					sql.append(" order by t.billing_time desc");
					SELECT(sql.toString());
					System.out.println(">>:"+sql.toString());

				}
			}.toString();
		}
	}

	public static class createSQL1 {
		public static String queryAmBillingInfoDTOsSQL1(@Param("amBilingCondition")AmBillingCondition amBilingCondition) {
			return new SQL() {
				{
					StringBuilder sql = new StringBuilder();
					sql.append("* from");
					sql.append("  (select  t.objuid,");
					sql.append("         t.pacr_no,");
					sql.append("         t.aircraft_type,");
					sql.append("         t.part_drawing_no,");
					sql.append("         t.part_name,");
					sql.append("         t.dispatch_no,");
					sql.append("         t.lupihao,");
					sql.append("         t.quantity,");
					sql.append("         t.handover_category,");
					sql.append("         t.workshop_name,");
					sql.append("         t.billing_user,");
					sql.append("         t.billing_deft,");
					sql.append("         t.billing_time,");
					sql.append("         t.receive_user,");
					sql.append("         t.receive_dept,");
					sql.append("         t.receive_time,");
					sql.append("         t.receive_notes,");
					sql.append("         t.receive_state,");
					sql.append("         t.factory_id,");
					sql.append("         t.billing_notes,");
					sql.append("         t.current_process_no,");
					sql.append("         t.current_process_name,");
					sql.append("         t.refuse_reason,t.c_user,t.c_time,t.m_user,t.m_time,");
					sql.append("		 t1.name billing_user_name,");//开单人姓名
					sql.append("		 t2.name receive_user_name,");//接单人
					sql.append("		 t3.dept_name billing_deft_name,");//开单部门
					sql.append("		 t4.dept_name receive_dept_name, ");//接单部门
					sql.append("		 t5.category_name handoverCategoryName, ");//移交类别
					sql.append("		 t6.workshop_name workshopNameName, ");//外表车间
					sql.append("		 t7.dept_name place_dept_name, ");//所在部门
					sql.append("row_number() over(partition by t.pacr_no order by c_time desc) aa ");
					sql.append("    from am_billing_info t ");
					sql.append("    left join jcwh_stk_category t5 on t.handover_category=t5.id  ");
					sql.append("    left join jcwh_stk_workshop t6 on t.workshop_name=t6.id  ");
//					sql.append("    left join mes_order_trace a on t.pacr_no = a.fabrecordid");
//					sql.append("   left join ( select ordertraceuid,onesoknum,goodnum,row_number() over (order by lcheckertime ) m from mes_process_trace ) b on a.objuid = b.ordertraceuid ");
					sql.append("	left join stf_employee t1 on t.billing_user=t1.objuid ");
					sql.append("	left join stf_employee t2 on t.receive_user=t2.objuid ");
					sql.append("	left join stf_dept t3 on t.billing_deft=t3.objuid ");
					sql.append("	left join stf_dept t4 on t.receive_dept=t4.objuid ");
					sql.append("	left join stf_dept t7 on t.receive_dept=t7.objuid ");
					sql.append("	where 1=1 ");
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getFactoryId())) {//厂代号
						sql.append(" and t.factory_id = #{amBilingCondition.factoryId, jdbcType=VARCHAR}");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getPacrNo())) {//条码号
						sql.append(" and upper(t.pacr_no) = upper(#{amBilingCondition.pacrNo, jdbcType=VARCHAR})");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getAircraftType())) {//机型
						sql.append(" and t.aircraft_type = #{amBilingCondition.aircraftType, jdbcType=VARCHAR}");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getPartDrawingNo())) {//零件图号
//						sql.append(" and t.part_drawing_no = #{amBilingCondition.partDrawingNo, jdbcType=VARCHAR}");
						sql.append(" and t.part_drawing_no like concat(concat('%',#{amBilingCondition.partDrawingNo, jdbcType=VARCHAR}),'%')");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getHandoverCategory())) {//移交类别
						sql.append(" and t.handover_category = #{amBilingCondition.handoverCategory, jdbcType=VARCHAR}");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getWorkshopName())) {//外表车间
						sql.append(" and t.workshop_name = #{amBilingCondition.workshopName, jdbcType=VARCHAR}");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getBillingUser())) {//开单人
//						sql.append(" and t.billing_user = #{amBilingCondition.billingUser, jdbcType=VARCHAR}");
						sql.append(" and t1.name like concat(concat('%',#{amBilingCondition.billingUser, jdbcType=VARCHAR}),'%')");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getBillingDeft())) {//开单部门
						sql.append(" and t.billing_deft = #{amBilingCondition.billingDeft, jdbcType=VARCHAR}");
					}
					if(amBilingCondition.getBillingTime()!= null) {//开单时间段
//						sql.append(" and to_date(to_char(t.billing_time,'yyyy-MM-dd'),'yyyy-MM-dd') >= to_date(to_char(#{amBilingCondition.billingTime, jdbcType=TIMESTAMP},'yyyy-MM-dd'),'yyyy-MM-dd') ");
						sql.append(" and trunc(t.billing_time) >= trunc(#{amBilingCondition.billingTime, jdbcType=TIMESTAMP})");
					}
					if(amBilingCondition.getBillingTime2()!= null) {
//						sql.append(" and to_date(to_char(t.billing_time,'yyyy-MM-dd'),'yyyy-MM-dd') <= to_date(to_char(#{amBilingCondition.billingTime2, jdbcType=TIMESTAMP},'yyyy-MM-dd'),'yyyy-MM-dd') ");
						sql.append(" and trunc(t.billing_time) < trunc(#{amBilingCondition.billingTime2, jdbcType=TIMESTAMP})+1");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getReceiveUser())) {//接收人
//						sql.append(" and t.receive_user = #{amBilingCondition.receiveUser, jdbcType=VARCHAR}");
						sql.append(" and t2.name like concat(concat('%',#{amBilingCondition.receiveUser, jdbcType=VARCHAR}),'%')");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getReceiveDept())) {//接收部门
						sql.append(" and t.receive_dept = #{amBilingCondition.receiveDept, jdbcType=VARCHAR}");
					}
					if(amBilingCondition.getReceiveTime()!= null) {//接收时间段
						sql.append(" and t.receive_time >= #{amBilingCondition.receiveTime, jdbcType=TIMESTAMP}");
					}
					if(amBilingCondition.getReceiveTime2()!= null) {
						sql.append(" and t.receive_time <= #{amBilingCondition.receiveTime2, jdbcType=TIMESTAMP}");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getReceiveState())) {//状态
						sql.append(" and t.receive_state = #{amBilingCondition.receiveState, jdbcType=VARCHAR}");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getRefuseReason())) {//拒绝原因
						sql.append(" and t.refuse_reason = #{amBilingCondition.refuseReason, jdbcType=VARCHAR}");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getPlaceDept())) {//所在部门
						sql.append(" and t.receive_dept = #{amBilingCondition.placeDept, jdbcType=VARCHAR} and t.receive_state = 5");
					}
					sql.append(") t where t.aa = 1");
					sql.append(" order by t.billing_time desc");
					SELECT(sql.toString());
					System.out.println(">>:"+sql.toString());

				}
			}.toString();
		}
	}

	public static class createSQL2 {
		public static String queryAmBillingInfoDTOsSQL2(@Param("amBilingCondition")AmBillingCondition amBilingCondition) {
			return new SQL() {
				{
					StringBuilder sql = new StringBuilder();
					sql.append("   t.objuid as key,t.objuid,");
					sql.append("         t.pacr_no,");
					sql.append("         t.aircraft_type,");
					sql.append("         t.part_drawing_no,");
					sql.append("         t.part_name,");
					sql.append("         t.dispatch_no,");
					sql.append("         t.lupihao,");
					sql.append("         t.quantity,");
					sql.append("         t.handover_category,");
					sql.append("         t.workshop_name,");
					sql.append("         t.billing_user,");
					sql.append("         t.billing_deft,");
					sql.append("         t.billing_time,");
					sql.append("         t.receive_user,");
					sql.append("         t.receive_dept,");
					sql.append("         t.receive_time,");
					sql.append("         t.receive_notes,");
					sql.append("         t.receive_state,");
					sql.append("         t.factory_id,");
					sql.append("         t.billing_notes,");
					sql.append("         t.current_process_no,");
					sql.append("         t.current_process_name,");
					sql.append("         t.refuse_reason,t.c_user,t.c_time,t.m_user,t.m_time,");
					sql.append("		 t1.name billing_user_name,");//开单人姓名
					sql.append("		 t2.name receive_user_name,");//接单人
					sql.append("		 t3.dept_name billing_deft_name,");//开单部门
					sql.append("		 t4.dept_name receive_dept_name, ");//接单部门
					sql.append("		 t5.category_name handoverCategoryName, ");//移交类别
					sql.append("		 t6.workshop_name workshopNameName, ");//外表车间
					sql.append("    0 as pageNo,10 as pageSize from am_billing_info t ");
					sql.append("    left join jcwh_stk_category t5 on t.handover_category=t5.id  ");
					sql.append("    left join jcwh_stk_workshop t6 on t.workshop_name=t6.id  ");
					sql.append("	left join stf_employee t1 on t.billing_user=t1.objuid ");
					sql.append("	left join stf_employee t2 on t.receive_user=t2.objuid ");
					sql.append("	left join stf_dept t3 on t.billing_deft=t3.objuid ");
					sql.append("	left join stf_dept t4 on t.receive_dept=t4.objuid ");
					sql.append("	where 1=1 ");
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getFactoryId())) {//条码号
						sql.append(" and t.factory_id = #{amBilingCondition.factoryId, jdbcType=VARCHAR}");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getPacrNo())) {//条码号
						sql.append(" and upper(t.pacr_no) = upper(#{amBilingCondition.pacrNo, jdbcType=VARCHAR})");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getAircraftType())) {//机型
						sql.append(" and t.aircraft_type = #{amBilingCondition.aircraftType, jdbcType=VARCHAR}");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getPartDrawingNo())) {//零件图号
						sql.append(" and t.part_drawing_no like concat(concat('%',#{amBilingCondition.partDrawingNo, jdbcType=VARCHAR}),'%')");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getHandoverCategory())) {//移交类别
						sql.append(" and t.handover_category = #{amBilingCondition.handoverCategory, jdbcType=VARCHAR}");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getWorkshopName())) {//外表车间
						sql.append(" and t.workshop_name = #{amBilingCondition.workshopName, jdbcType=VARCHAR}");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getBillingUser())) {//开单人
						sql.append(" and t1.name like concat(concat('%',#{amBilingCondition.billingUser, jdbcType=VARCHAR}),'%')");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getBillingDeft())) {//开单部门
						sql.append(" and t.billing_deft = #{amBilingCondition.billingDeft, jdbcType=VARCHAR}");
					}
					if(amBilingCondition.getBillingTime()!= null) {//开单时间段
						sql.append(" and trunc(t.billing_time) >= trunc(#{amBilingCondition.billingTime, jdbcType=TIMESTAMP})");
					}
					if(amBilingCondition.getBillingTime2()!= null) {
						sql.append(" and trunc(t.billing_time) < trunc(#{amBilingCondition.billingTime2, jdbcType=TIMESTAMP})+1");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getReceiveUser())) {//接收人
						sql.append(" and t2.name like concat(concat('%',#{amBilingCondition.receiveUser, jdbcType=VARCHAR}),'%')");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getReceiveDept())) {//接收部门
						sql.append(" and t.receive_dept = #{amBilingCondition.receiveDept, jdbcType=VARCHAR}");
					}
					if(amBilingCondition.getReceiveTime()!= null) {//接收时间段
						sql.append(" and t.receive_time >= #{amBilingCondition.receiveTime, jdbcType=TIMESTAMP}");
					}
					if(amBilingCondition.getReceiveTime2()!= null) {
						sql.append(" and t.receive_time <= #{amBilingCondition.receiveTime2, jdbcType=TIMESTAMP}");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getReceiveState())) {//状态
						sql.append(" and t.receive_state = #{amBilingCondition.receiveState, jdbcType=VARCHAR}");
					}
					if(!StrUtil.isEmptyOrUndefined(amBilingCondition.getRefuseReason())) {//拒绝原因
						sql.append(" and t.refuse_reason = #{amBilingCondition.refuseReason, jdbcType=VARCHAR}");
					}
					sql.append(" order by t.billing_time desc");
					SELECT(sql.toString());
					System.out.println(">>:"+sql.toString());

				}
			}.toString();
		}
	}
	
//    @Select("select " +
//    		"  a.fabrecordid pacr_no,"+
//    		"         a.jx aircraft_type,"+
//    		"         a.partid part_drawing_no,"+
//    		"         a.part_name,"+
//    		"         a.assignmentid dispatch_no,"+
//    		"         a.CURGONGXU currentProcessNo," +
//			"		  a.CURGONGXUNAME currentProcessName,"+
//    		"         decode(b.goodnum, 0, b.onesoknum, b.goodnum) quantity"+
//    		"    from mes_order_trace a"+
//    		"    left join mes_process_trace b on a.objuid = b.ordertraceuid"+
//    		"   where a.fabrecordid = #{amBilingCondition.pacrNo, jdbcType=VARCHAR}"+
//    		"     and b.lcheckertime ="+
//    		"         (select max(lcheckertime)"+
//    		"            from mes_process_trace c"+
//    		"            left join mes_order_trace d on c.ordertraceuid = d.objuid"+
//    		"           where d.fabrecordid = #{amBilingCondition.pacrNo, jdbcType=VARCHAR} )")
//	@Select("select \n" +
//			"objuid,pacr_no,aircraft_type,part_drawing_no,part_name,dispatch_no,lupihao,currentProcessNo,currentProcessName,quantity\n" +
//			"from view_gongduanyijiao_zzjl " +
//			" where pacr_no = #{amBilingCondition.pacrNo, jdbcType=VARCHAR} ")
//	AmBillingInfoDTO queryMesOrderTraceInfo(@Param("amBilingCondition")AmBillingCondition amBillingCondition);
// 通过制造记录条码查询制造记录信息
	@Select("select a.objuid,\n" +
			"       a.pacr_no,\n" +
			"       a.aircraft_type,\n" +
			"       a.part_drawing_no,\n" +
			"       a.part_name,\n" +
			"       a.dispatch_no,\n" +
			"       a.lupihao,\n" +
			"       a.currentProcessNo,\n" +
			"       a.currentProcessName,\n" +
			"       a.quantity,\n" +
			"       b.factory_id,\n" +
			"       b.objuid dykhxobj\n" +
			"  from view_gongduanyijiao_zzjl a\n" +
			"  left join mes_gdyj_dykhx b\n" +
			"    on a.part_drawing_no = b.part_drawing_no and b.factory_id = #{facCode, jdbcType=VARCHAR} and b.delete_tag = 0 \n" +
			" where pacr_no = #{amBilingCondition.pacrNo, jdbcType=VARCHAR} ")
	AmBillingInfoDTO queryMesOrderTraceInfo(@Param("amBilingCondition")AmBillingCondition amBillingCondition, @Param("facCode")String facCode);

	@Select("select count(*) from am_billing_info where pacr_no = #{pacrno,jdbcType=VARCHAR} and receive_state= #{receiveState,jdbcType=VARCHAR}")
	int queryCountBypacrnoAndReceivestate(@Param("pacrno") String pacrno, @Param("receiveState") Integer receiveState);

	//新增开单信息
    @Insert("    insert into am_billing_info" +   
    		"      (objuid, pacr_no, aircraft_type, part_drawing_no, part_name, dispatch_no, quantity, handover_category, workshop_name, billing_user, billing_deft, billing_time,factory_id,c_user, c_time, " +
			"billing_notes, current_process_no,current_process_name,receive_state,receive_dept,lupihao)" +
    		"    values" +   
    		"      (#{objuid, jdbcType=VARCHAR}, #{pacrNo, jdbcType=VARCHAR}, #{aircraftType, jdbcType=VARCHAR}, #{partDrawingNo, jdbcType=VARCHAR}, #{partName, jdbcType=VARCHAR}, #{dispatchNo, jdbcType=VARCHAR}, #{quantity, jdbcType=DOUBLE}, " +
			"#{handoverCategory, jdbcType=DOUBLE}, #{workshopName, jdbcType=VARCHAR}, #{billingUser, jdbcType=VARCHAR}, #{billingDeft, jdbcType=VARCHAR}, sysdate,#{factoryId, jdbcType=VARCHAR}, #{cUser, jdbcType=VARCHAR}, sysdate, " +
			"#{billingNotes, jdbcType=VARCHAR},#{currentProcessNo, jdbcType=VARCHAR},#{currentProcessName, jdbcType=VARCHAR}," +
			"#{receiveState, jdbcType=INTEGER}, #{receiveDept, jdbcType=VARCHAR}, #{lupihao, jdbcType=VARCHAR})"
    		)
	int insertAmBillingInfo(AmBillingInfoDO amBillingInfoDO);
    
    @Select("select a.objuid,a.emp_id,a.emp_name,a.dept_uid from fac_employee a where a.objuid = #{objuid}")
    EmpInfoDTO  queryEmpInfo(@Param("objuid")String loginUid);

	//通过制造记录条码查询已经开单但未接单的信息
    @Select("select objuid,\r\n"
    		+ "       pacr_no,\r\n"
    		+ "       aircraft_type,\r\n"
    		+ "       part_drawing_no,\r\n"
    		+ "       part_name,\r\n"
    		+ "       dispatch_no,\r\n"
    		+ "       quantity,\r\n"
    		+ "       handover_category,\r\n"
    		+ "       workshop_name,\r\n"
    		+ "       billing_user,\r\n"
    		+ "       billing_deft,\r\n"
    		+ "       billing_time,\r\n"
    		+ "       receive_user,\r\n"
    		+ "       receive_dept,\r\n"
    		+ "       receive_time,\r\n"
    		+ "       receive_notes,\r\n"
    		+ "       receive_state,\r\n"
			+ "       lupihao\r\n"
    		+ "  from am_billing_info where receive_state = 5 and pacr_no = #{amBilingCondition.pacrNo, jdbcType=VARCHAR} ")
	AmBillingInfoDTO queryReceiveAmBillingInfo(@Param("amBilingCondition")AmBillingCondition amBillingCondition);

	//撤回已开单但是未接单的开单信息
    @Delete("delete from am_billing_info where objuid = #{amBilingCondition.objuid, jdbcType=VARCHAR} ")
	void deleteAmBillingInfoRecall(@Param("amBilingCondition")AmBillingInfoDO amBillingInfoDO);

	//接单
    @Update("update am_billing_info\r\n"
    		+ "   set\r\n"
    		+ "       receive_user      = #{amBilingCondition.receiveUser, jdbcType=VARCHAR},\r\n"
    		+ "       receive_time      = sysdate,\r\n"
    		+ "       receive_dept     = #{amBilingCondition.receiveDept, jdbcType=VARCHAR},\r\n"
    		+ "       receive_state     = #{amBilingCondition.receiveState, jdbcType=VARCHAR},\r\n"
    		+ "       refuse_reason     = #{amBilingCondition.refuseReason, jdbcType=VARCHAR},\r\n"
    		+ "       m_user            = #{amBilingCondition.mUser, jdbcType=VARCHAR},\r\n"
    		+ "       m_time            = sysdate\r\n"
    		+ " where objuid = #{amBilingCondition.objuid, jdbcType=VARCHAR}")
	void updateAmbillingInfoReceive(@Param("amBilingCondition")AmBillingInfoDO amBillingInfoDO);

	//接单-拒绝
    @Update("update am_billing_info\r\n"
    		+ "   set\r\n"
    		+ "       receive_user      = #{amBilingCondition.receiveUser, jdbcType=VARCHAR},\r\n"
    		+ "       receive_time      = sysdate,\r\n"
    		+ "       receive_dept     = #{amBilingCondition.receiveDept, jdbcType=VARCHAR},\r\n"
    		+ "       receive_state     = #{amBilingCondition.receiveState, jdbcType=VARCHAR},\r\n"
    		+ "       refuse_reason     = #{amBilingCondition.refuseReason, jdbcType=VARCHAR},\r\n"
    		+ "       m_user            = #{amBilingCondition.mUser, jdbcType=VARCHAR},\r\n"
    		+ "       m_time            = sysdate\r\n"
    		+ " where objuid = #{amBilingCondition.objuid, jdbcType=VARCHAR}")
	void updateAmbillingInfoRefuse(@Param("amBilingCondition")AmBillingInfoDO amBillingInfoDO);

	@Update("update am_billing_info\r\n"
			+ "   set\r\n"
			+ "       receive_state     = #{amBilingCondition.receiveState, jdbcType=VARCHAR},\r\n"
			+ "       m_user            = #{amBilingCondition.mUser, jdbcType=VARCHAR},\r\n"
			+ "       m_time            = sysdate\r\n"
			+ " where objuid = #{amBilingCondition.objuid, jdbcType=VARCHAR}")
	void updateAmbillingInfoRevert(@Param("amBilingCondition")AmBillingInfoDO amBillingInfoDO);

	//撤回已开单但是未接单的开单信息
    @Delete("delete from am_billing_info where objuid = #{objuid, jdbcType=VARCHAR} ")
	void deleteAmBillingInfoRecallSingle(String objuid);
    @Results(id = "QueryBillingInfoToReceive", value = {
    		 @Result(id = true, column = "objuid", property = "objuid", jdbcType = JdbcType.VARCHAR),
             @Result(column = "c_time", property = "createTime", typeHandler = LocalDateTypeHandler.class),
             @Result(column = "m_time", property = "modifyTime", typeHandler = LocalDateTypeHandler.class),
    })
//    @Select("select t.* from am_billing_info t " +
//			"where upper(t.pacr_no) = upper(#{pacrNo, jdbcType=VARCHAR}) and t.receive_state = 0")

	//接单-查询
	@Select("SELECT\n" +
			"\tt.objuid,\n" +
			"\tt.pacr_no,\n" +
			"\tt.aircraft_type,\n" +
			"\tt.part_drawing_no,\n" +
			"\tt.part_name,\n" +
			"\tt.dispatch_no,\n" +
			"\tt.lupihao,\n" +
			"\tt.quantity,\n" +
			"\tt.handover_category,\n" +
			"\tt.workshop_name,\n" +
			"\tt.billing_user,\n" +
			"\tt.billing_deft,\n" +
			"\tt.billing_time,\n" +
			"\tt.receive_user,\n" +
			"\tt.receive_dept,\n" +
			"\tt.receive_time,\n" +
			"\tt.receive_notes,\n" +
			"\tt.receive_state,\n" +
			"\tt.factory_id,\n" +
			"\tt.billing_notes,\n" +
			"\tt.current_process_no,\n" +
			"\tt.current_process_name,\n" +
			"\tt.refuse_reason,\n" +
			"\tt.c_user,\n" +
			"\tt.c_time,\n" +
			"\tt.m_user,\n" +
			"\tt.m_time,\n" +
			"\tt1.name billing_user_name,\n" +
			"\tt2.name receive_user_name,\n" +
			"\tt3.dept_name billing_deft_name,\n" +
			"\tt4.dept_name receive_dept_name,\n" +
			"\tt5.category_name handoverCategoryName,\n" +
			"\tt6.workshop_name workshopNameName \n" +
			"FROM\n" +
			"\tam_billing_info t\n" +
			"\tLEFT JOIN jcwh_stk_category t5 ON t.handover_category = t5.id\n" +
			"\tLEFT JOIN jcwh_stk_workshop t6 ON t.workshop_name = t6.id\n" +
			"\tLEFT JOIN stf_employee t1 ON t.billing_user = t1.objuid\n" +
			"\tLEFT JOIN stf_employee t2 ON t.receive_user = t2.objuid\n" +
			"\tLEFT JOIN stf_dept t3 ON t.billing_deft = t3.objuid\n" +
			"\tLEFT JOIN stf_dept t4 ON t.receive_dept = t4.objuid\n" +
			"where upper(t.pacr_no) = upper(#{pacrNo, jdbcType=VARCHAR}) and t.receive_state = 0")
	AmBillingInfoDTO queryBillingInfoByPacrNo(@Param("pacrNo")String pacrNo);

	/**
	 * 2022-07-22
	 * 根据制造记录条码更新登记账是否移交字段
	 * @param pacrNo
	 */
	@Update("update mes_wx_djz a set a.transfer = 1\n" +
			"where a.fabrecordid = #{pacrNo, jdbcType=VARCHAR}")
    void updateDjz(@Param("pacrNo")String pacrNo);
}
